#!/bin/bash

# 配置MySQL远程连接

set -e

# 颜色定义
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
BLUE='\033[0;34m'
NC='\033[0m' # No Color

log_info() {
    echo -e "${BLUE}[INFO]${NC} $1"
}

log_success() {
    echo -e "${GREEN}[SUCCESS]${NC} $1"
}

log_warning() {
    echo -e "${YELLOW}[WARNING]${NC} $1"
}

log_error() {
    echo -e "${RED}[ERROR]${NC} $1"
}

# 检查root权限
check_root() {
    if [[ $EUID -ne 0 ]]; then
        log_error "此脚本必须以root权限运行"
        exit 1
    fi
}

# 获取MySQL root密码
get_mysql_password() {
    if [[ -f /root/mysql_passwords.txt ]]; then
        MYSQL_ROOT_PASSWORD=$(grep "MySQL Root Password" /root/mysql_passwords.txt | awk '{print $4}')
        if [[ -z "$MYSQL_ROOT_PASSWORD" ]]; then
            log_error "无法从密码文件获取MySQL root密码"
            read -s -p "请输入MySQL root密码: " MYSQL_ROOT_PASSWORD
            echo
        fi
    else
        read -s -p "请输入MySQL root密码: " MYSQL_ROOT_PASSWORD
        echo
    fi
    
    # 验证密码
    if ! mysql -u root -p"$MYSQL_ROOT_PASSWORD" -e "SELECT 1;" &> /dev/null; then
        log_error "MySQL root密码错误"
        exit 1
    fi
}

# 配置MySQL允许远程连接
configure_mysql_remote() {
    log_info "配置MySQL允许远程连接..."
    
    # 备份MySQL配置
    cp /etc/mysql/mysql.conf.d/mysqld.cnf /etc/mysql/mysql.conf.d/mysqld.cnf.backup.$(date +%Y%m%d)
    
    # 修改MySQL配置
    sed -i 's/^bind-address.*=.*127.0.0.1/# bind-address = 127.0.0.1/' /etc/mysql/mysql.conf.d/mysqld.cnf
    sed -i 's/^# bind-address.*=.*0.0.0.0/bind-address = 0.0.0.0/' /etc/mysql/mysql.conf.d/mysqld.cnf
    
    # 如果上面没找到，直接添加
    if ! grep -q "bind-address.*=.*0.0.0.0" /etc/mysql/mysql.conf.d/mysqld.cnf; then
        echo "bind-address = 0.0.0.0" >> /etc/mysql/mysql.conf.d/mysqld.cnf
    fi
    
    log_success "MySQL配置已更新"
}

# 创建远程访问用户
create_remote_user() {
    log_info "创建远程访问用户..."
    
    local remote_user="wordpress_remote"
    local remote_password=$(openssl rand -base64 16)
    
    # 创建远程用户
    mysql -u root -p"$MYSQL_ROOT_PASSWORD" <<EOF
CREATE USER IF NOT EXISTS '$remote_user'@'%' IDENTIFIED BY '$remote_password';
GRANT ALL PRIVILEGES ON wordpress.* TO '$remote_user'@'%';
GRANT SELECT, PROCESS ON *.* TO '$remote_user'@'%';
FLUSH PRIVILEGES;
EOF

    # 保存远程用户密码
    echo "MySQL Remote User: $remote_user" >> /root/mysql_passwords.txt
    echo "MySQL Remote Password: $remote_password" >> /root/mysql_passwords.txt
    
    log_success "远程用户创建完成"
    log_info "远程用户名: $remote_user"
    log_info "远程用户密码: $remote_password"
}

# 配置防火墙
configure_firewall() {
    log_info "配置防火墙允许MySQL端口..."
    
    # 检查ufw是否安装
    if command -v ufw > /dev/null; then
        ufw allow 3306/tcp
        log_success "防火墙已配置允许3306端口"
    else
        log_warning "ufw未安装，请手动配置防火墙"
    fi
}

# 安全配置
configure_security() {
    log_info "配置安全设置..."
    
    # 修改root用户允许远程连接（可选，不推荐）
    read -p "是否允许root用户远程连接? (不推荐) (y/n): " -n 1 -r
    echo
    if [[ $REPLY =~ ^[Yy]$ ]]; then
        mysql -u root -p"$MYSQL_ROOT_PASSWORD" <<EOF
CREATE USER IF NOT EXISTS 'root'@'%' IDENTIFIED BY '$MYSQL_ROOT_PASSWORD';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
EOF
        log_warning "已允许root用户远程连接（安全风险！）"
    fi
    
    # 限制连接次数
    mysql -u root -p"$MYSQL_ROOT_PASSWORD" <<EOF
INSTALL PLUGIN CONNECTION_CONTROL SONAME 'connection_control.so';
INSTALL PLUGIN CONNECTION_CONTROL_FAILED_LOGIN_ATTEMPTS SONAME 'connection_control.so';
SET GLOBAL connection_control_failed_connections_threshold = 3;
SET GLOBAL connection_control_min_connection_delay = 1000;
EOF
}

# 重启MySQL服务
restart_mysql() {
    log_info "重启MySQL服务..."
    
    systemctl restart mysql
    
    # 等待MySQL启动
    sleep 3
    
    if systemctl is-active --quiet mysql; then
        log_success "MySQL服务重启成功"
    else
        log_error "MySQL服务重启失败"
        exit 1
    fi
}

# 测试远程连接
test_remote_connection() {
    log_info "测试远程连接..."
    
    local server_ip=$(curl -s icanhazip.com 2>/dev/null || hostname -I | awk '{print $1}')
    local remote_user="wordpress_remote"
    local remote_password=$(grep "MySQL Remote Password" /root/mysql_passwords.txt | awk '{print $4}')
    
    log_info "服务器IP: $server_ip"
    log_info "测试命令: mysql -h $server_ip -u $remote_user -p"
    log_info "端口: 3306"
    
    # 本地测试远程连接
    if mysql -h 127.0.0.1 -u "$remote_user" -p"$remote_password" -e "SELECT 1;" &> /dev/null; then
        log_success "本地远程连接测试成功"
    else
        log_error "本地远程连接测试失败"
    fi
}

# 显示连接信息
show_connection_info() {
    log_success "=== MySQL远程连接配置完成 ==="
    echo ""
    
    local server_ip=$(curl -s icanhazip.com 2>/dev/null || hostname -I | awk '{print $1}')
    local remote_user="wordpress_remote"
    local remote_password=$(grep "MySQL Remote Password" /root/mysql_passwords.txt | awk '{print $4}')
    
    echo "连接信息:"
    echo "主机: $server_ip"
    echo "端口: 3306"
    echo "数据库: wordpress"
    echo ""
    echo "用户账户:"
    echo "1. 远程用户:"
    echo "   - 用户名: $remote_user"
    echo "   - 密码: $remote_password"
    echo "   - 权限: wordpress数据库全部权限"
    echo ""
    
    if grep -q "root@%" /root/mysql_passwords.txt; then
        echo "2. Root用户 (不推荐):"
        echo "   - 用户名: root"
        echo "   - 密码: $MYSQL_ROOT_PASSWORD"
        echo "   - 权限: 全部权限"
        echo ""
    fi
    
    echo "安全提醒:"
    echo "✓ 仅允许特定IP访问（建议）"
    echo "✓ 定期更改密码"
    echo "✓ 使用SSL连接（推荐）"
    echo "✓ 监控数据库日志"
    echo ""
    echo "密码文件: /root/mysql_passwords.txt"
}

# 配置特定IP访问（可选）
configure_ip_restriction() {
    log_info "配置特定IP访问限制..."
    
    read -p "是否限制特定IP访问? (y/n): " -n 1 -r
    echo
    if [[ $REPLY =~ ^[Yy]$ ]]; then
        read -p "请输入允许访问的IP地址 (如: 192.168.1.100 或 192.168.1.0/24): " allowed_ip
        
        if [[ -n "$allowed_ip" ]]; then
            mysql -u root -p"$MYSQL_ROOT_PASSWORD" <<EOF
DROP USER IF EXISTS 'wordpress_remote'@'%';
CREATE USER 'wordpress_remote'@'$allowed_ip' IDENTIFIED BY '$remote_password';
GRANT ALL PRIVILEGES ON wordpress.* TO 'wordpress_remote'@'$allowed_ip';
FLUSH PRIVILEGES;
EOF
            log_success "已限制仅允许IP: $allowed_ip 访问"
        fi
    fi
}

# 主函数
main() {
    check_root
    
    log_info "开始配置MySQL远程连接..."
    
    # 获取MySQL密码
    get_mysql_password
    
    # 配置远程连接
    configure_mysql_remote
    
    # 创建远程用户
    create_remote_user
    
    # 配置防火墙
    configure_firewall
    
    # 安全配置
    configure_security
    
    # 配置IP限制（可选）
    # configure_ip_restriction
    
    # 重启MySQL
    restart_mysql
    
    # 测试连接
    test_remote_connection
    
    # 显示连接信息
    show_connection_info
    
    log_success "MySQL远程连接配置完成！"
}

# 执行主函数
main "$@"